﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Text;

namespace DotNetMigrations
{
	public class SchemaInfoTable
	{
		/// <summary>
		/// Get the current schema version of the database.
		/// Creates the [schema_info] table if it doesn't already exist.
		/// </summary>
		public static long GetSchemaVersion()
		{
			if (!Exists())
			{
				Create();
				return 0;
			}

			object obj = DAL.ExecuteScalar("SELECT MAX([version]) FROM [schema_migrations]");
			long vers;

			if (obj != null && long.TryParse(obj.ToString(), out vers))
				return vers;
			else
				return 0;
		}

        /// <summary>
        /// Updates the schema_migrations table by rolling the version back to the target version.
        /// </summary>
        /// <param name="version">The version to roll back to</param>
        public static void TearDownVersion(long version)
        {
            string sql = " DELETE FROM [schema_migrations] WHERE [version] > '" + version.ToString() + "'";
            DAL.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// Updates the schema_migrations table by inserting rows into table up to the target version
        /// </summary>
        /// <param name="versions">The list of versions to add.</param>
        public static void SetupVersion(List<long> versions)
        {
            string sql = "";
            foreach (long version in versions)
            {
                sql += "INSERT INTO [schema_migrations] ([version]) VALUES ('" + version + "');";
            }

            DAL.ExecuteNonQuery(sql);
        }

		/// <summary>
		/// Checks if the [schema_info] table exists in the database.
		/// </summary>
		private static bool Exists()
		{
			string sql = "SELECT [version] FROM [schema_migrations]";

			object obj = DAL.ExecuteScalar(sql, true);
			int i;
			if (obj != null && int.TryParse(obj.ToString(), out i))
				return (i >= 0);
			else
				return false;
		}

		/// <summary>
		/// Creates the [schema_migrations] table in the database.
		/// </summary>
		private static void Create()
		{
            DAL.ExecuteNonQuery("CREATE TABLE [schema_migrations]([version] [char](14) NOT NULL PRIMARY KEY)");
            DAL.ExecuteNonQuery("INSERT INTO [schema_migrations] ([version]) VALUES (0)");

            if (OldTableExists())
            {
                MigrateToNewTable();
            }
		}

        /// <summary>
        /// Checks to see if a previous table version exists.
        /// </summary>
        /// <returns>True if the older table exists.</returns>
        private static bool OldTableExists()
        {
            string sql = "SELECT [version] FROM [schema_info]";

            object obj = DAL.ExecuteScalar(sql, true);
            int i;
            if (obj != null && int.TryParse(obj.ToString(), out i))
                return (i >= 0);
            else
                return false;
        }

        /// <summary>
        /// Coordinates the migration of versioning information from 
        /// the old schema_info table to the new schema_migrations table.
        /// </summary>
        private static void MigrateToNewTable()
        {
            Int64 latestVersionFromDb = GetLatestVersionFromDb();

            AddOldVersionsToNewTable(latestVersionFromDb);

            DAL.ExecuteNonQuery("DROP TABLE [schema_info]");
        }

        /// <summary>
        /// Grabs the latest version from the old schema_info table
        /// </summary>
        /// <returns>Returns the latest version</returns>
        private static long GetLatestVersionFromDb()
        {
            string sql = "SELECT [version] FROM [schema_info]";

            return long.Parse(DAL.ExecuteScalar(sql).ToString());
        }

        /// <summary>
        /// Loops through the script files and adds the records 
        /// to the schema_migrations table in ascending order.
        /// </summary>
        /// <param name="latestVersion">The current version being used.</param>
        /// <remarks>
        /// The logic assumes that all scripts located in the Migrations
        /// directory with a version number less than or equal to the current
        /// version has already been applied.
        /// </remarks>
        private static void AddOldVersionsToNewTable(long latestVersion)
        {
            if (!Directory.Exists(Program.MigrationDir))
                return;

            List<string> files = new List<string>();
            files.AddRange(Directory.GetFiles(Program.MigrationDir, "*.sql"));
            files.Sort();

            foreach (string file in files)
            {
                string fileName = Path.GetFileName(file);
                string sVers = fileName.Split('_')[0];
                long iVers;
                if (long.TryParse(sVers, out iVers))
                {
                    if (iVers <= latestVersion)
                    {
                        string sql = "INSERT INTO [schema_migrations]([version]) VALUES (" + iVers + ")";
                        DAL.ExecuteNonQuery(sql);
                    }
                }
            }
        }

        /// <summary>
        /// Obtains a list of version numbers given the starting/min version and the max version
        /// </summary>
        /// <param name="minVersion">The minimum version to look for.  Exclusive</param>
        /// <param name="maxVersion">The maximum version to look for. Inclusive</param>
        /// <returns>A listof all versions between the 2 values; including only the maxVersion.</returns>
        public static List<long> GetVersionList(long minVersion, long maxVersion)
        {
            if (!Directory.Exists(Program.MigrationDir))
                return null;

            List<long> versions = new List<long>();

            List<string> files = new List<string>();
            files.AddRange(Directory.GetFiles(Program.MigrationDir, "*.sql"));
            files.Sort();

            foreach (string file in files)
            {
                string fileName = Path.GetFileName(file);
                string sVers = fileName.Split('_')[0];
                long iVers;
                if (long.TryParse(sVers, out iVers))
                {
                    if (iVers > minVersion && iVers <= maxVersion)
                    {
                      versions.Add(iVers);
                    }
                }
            }

            return versions;
        }
	}
}
